﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Model;


namespace DALayer
{
    public class LongSortDAL
    {

        //搜索城市表
        public static DataTable SearchCity()
        {
            string sql = $"select * from City ";
            return DBHelper.SelectByAdapter(sql);
        }


        //长途车主添加
        public static int Add(LongCarShareInfo ls)
        {
            string sql = $"insert into LongCarShare(UserID,LGoCity,LDesitination,LGoDate," +
                $"LCarType,LPPeople,LContain,LCost,LPType,LCWord,LStatus,LPTime) " +
                $"values('{ls.UserID}','{ls.LGoCity}','{ls.Distination}','{ls.LGoDate}','{ls.LCarType}'," +
                $"{ls.LPPeople},{ls.LContain},'{ls.LCost}','{ls.LPType}','{ls.LCWord}','{ls.LStatus}','{ls.LPTime}')";
            return DBHelper.UTNonQuery(sql);
        }

        //长途乘客添加
        public static int PAdd(LongCarShareInfo ls)
        {
            string sql = $"insert into LongCarShare(UserID,LGoCity,LDesitination,LGoDate," +
                $"LPPeople,LCost,LPType,LCWord,LStatus,LPTime) " +
                $"values('{ls.UserID}','{ls.LGoCity}','{ls.Distination}','{ls.LGoDate}'," +
                $"{ls.LPPeople},'{ls.LCost}','{ls.LPType}','{ls.LCWord}','{ls.LStatus}','{ls.LPTime}')";
            return DBHelper.UTNonQuery(sql);
        }

        //短途车主添加
        public static int SAdd(ShortCarShare sc)
        {
            string sql = $"insert into ShortCarShare(UserID,SGoCity,SDesitionation,SPass,SCarType,SContain,SGoTime," +
                $"SPTime,SCWord,SStatus,SCost,SReturn) " +
                $"values('{sc.UserID}','{sc.SGoCity}','{sc.SDesitionation}','{sc.SPass}','{sc.SCarType}','{sc.SContain}','{sc.SGoTime}'," +
                $"'{sc.SPTime}','{sc.SCWord}','{sc.SStatus}','{sc.SCost}','{sc.SReturn}')";
            return DBHelper.UTNonQuery(sql);
        }

        //添加长途城市检索
        public static DataTable Search(LongCarShareInfo ls)
        {
            
            string sql = $"select* from City  where((select COUNT(CtID) from City where CtName = '{ls.LGoCity}') + (select COUNT(CtID) from City where CtName = '{ls.Distination}'))= 2";
            return DBHelper.SelectByAdapter(sql);
        }


    }
}
